package utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;


import beans.User;

public class DBAdapter {

    private static final String WHERECLAUSE = "account=";

    //数据库表名
    private static final String TABLENAME = "UserInfo";
    //建表语句
    private static String sql = "create table "+TABLENAME+
            " (account text primary key,"+
            " username text not null,"+
            " passwd text not null,"+
            " isVIP integer default 0);";

    private SQLiteDatabase sqldb;
    //    SQLiteOpenHelper是SQLiteDatabase的一个帮助类，
//    这个帮助类可以辅助建立、更新和打开数据库，避免很多繁琐的判断过程。
    private DBOpenHelper dbOpenHelper;
    private Context context;

    public DBAdapter(Context context) {
        this.context = context;
    }

    private static class DBOpenHelper extends SQLiteOpenHelper{

        public DBOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(sql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }

    public void open(){
        dbOpenHelper = new DBOpenHelper(context, "user.db",null,1);
        try {
            sqldb = dbOpenHelper.getWritableDatabase();
        } catch (Exception e) {
            sqldb = dbOpenHelper.getReadableDatabase();
        }
    }

    public void close(){
        if ( sqldb != null ){
            sqldb.close();
            sqldb = null;
        }
    }

    //插入操作
    public long insert(User user){
        ContentValues values = new ContentValues();

        values.put("account",user.getAccount());
        values.put("username",user.getUsername());
        values.put("passwd",user.getPasswd());
        values.put("isVIP",user.getIsVIP());

        return sqldb.insert(TABLENAME,null,values);
    }

    //删除操作
    public long delete(String email){
        return sqldb.delete(TABLENAME,WHERECLAUSE+email,null);
    }

    //删表操作(不安全)
    private long deleteAll(){
        return sqldb.delete(TABLENAME,null,null);
    }

    //更新操作
    public long update(String email,User user){
        ContentValues values = new ContentValues();

        values.put("account",user.getAccount());
        values.put("username",user.getUsername());
        values.put("passwd",user.getPasswd());
        values.put("isVIP",user.getIsVIP());

        return sqldb.update(TABLENAME,values,WHERECLAUSE+email,null);
    }

    //更新VIP状态
    public void updateIsVIP(String condition,String email){
        ContentValues values = new ContentValues();

        //解决大小不匹配写问题
        String new_condition = condition.toUpperCase();

        if ( new_condition.equals("ISNOTVIP") ){
            values.put("isVIP",0);
        }else if ( new_condition.equals("ISVIP") ){
            values.put("isVIP",1);
        }
        sqldb.update(TABLENAME,values,WHERECLAUSE+email,null);
    }

    //查询一个账号信息
    public User queryOneUser(String email){
        //查询结果(Android里以游标体现)
        Cursor cursor = sqldb.query(TABLENAME,null,WHERECLAUSE+"\""+email+"\"",null,
                null,null,null);
        User user = null;
        //数据集游标移到第一个元素
        if ( cursor.moveToFirst() ) {
            user = new User();
            user.setAccount(cursor.getString(0));
            user.setUsername(cursor.getString(1));
            user.setPasswd(cursor.getString(2));
            user.setIsVIP(cursor.getInt(3));
        }
        return user;
    }

    //查询所有账号(不安全)
    private User[] queryAll(){
        //查询结果(Android里以游标体现)
        Cursor cursor = sqldb.query(TABLENAME,null,null,null,
                null,null,null);

        int count = cursor.getCount();
        if ( count <= 0 ){
            return null;
        }else{
            //数据集游标移到第一个元素
            cursor.moveToFirst();
            User[] users = new User[count];
            for ( int i=0; i<count; i++ ){
                users[i] = new User();
                users[i].setAccount(cursor.getString(0));
                users[i].setUsername(cursor.getString(1));
                users[i].setPasswd(cursor.getString(2));
                users[i].setIsVIP(cursor.getInt(3));
                //数据集游标移动到下一个元素
                cursor.moveToNext();
            }
            return users;
        }
    }

}
